﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.Windows.Forms;
using lgmkr;

namespace Sklad1_2.Data
{
    public class DBase
    {
        private OleDbConnection _fConnection;
        private OleDbCommand _fCommand;
        private OleDbDataReader _fReader;
        private LogMaker aLogMaker;
        public DBase(string aConnectionString)
        {
            _fConnection = new OleDbConnection(aConnectionString);
            aLogMaker=new LogMaker();
        }
        #region AddValues
        
        public void AddItemDBGridView(DataGridView aDataGridView, string query)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = new OleDbCommand { CommandText = query };
            var adapter = new OleDbDataAdapter(_fCommand.CommandText, _fConnection);
            var aTable = new DataTable { Locale = CultureInfo.InvariantCulture };
            try
            {
                adapter.Fill(aTable);
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
                return;
            }
            aDataGridView.DataSource = aTable;
        }

        public void AddItemFirma(ComboBox aComboBox, string tableName)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = new OleDbCommand { CommandText = "select distinct * from " + tableName };
            var adapter = new OleDbDataAdapter(_fCommand.CommandText, _fConnection);
            var aTable = new DataTable { Locale = CultureInfo.InvariantCulture };
            try
            {
                adapter.Fill(aTable);
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            
            aComboBox.DataSource = aTable;
            aComboBox.DisplayMember = "firm_name";
            aComboBox.ValueMember = "ID";
        }

        public void AddItemCategory(ComboBox aComboBox, int id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = new OleDbCommand
            {
                CommandText =
                    "select distinct a.* from category a,angar b where b.id=a.sklad_id and b.id=" +
                    Convert.ToString(id)
            };
            var adapter = new OleDbDataAdapter(_fCommand.CommandText, _fConnection);
            var aTable = new DataTable { Locale = CultureInfo.InvariantCulture };
            try
            {
                adapter.Fill(aTable);
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
                return;
            }
            aComboBox.DataSource = aTable;
            aComboBox.DisplayMember = "description";
            aComboBox.ValueMember = "ID";
        }

        public void AddItem(ComboBox aComboBox, string tableName)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = new OleDbCommand { CommandText = "select distinct * from " + tableName };
            var adapter = new OleDbDataAdapter(_fCommand.CommandText, _fConnection);
            var aTable = new DataTable { Locale = CultureInfo.InvariantCulture };
            try
            {
                adapter.Fill(aTable);
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            aComboBox.DataSource = aTable;
            aComboBox.DisplayMember = "description";
            aComboBox.ValueMember = "ID";
        }

        public void AddItemProduct(ComboBox aComboBox, string tableName)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = new OleDbCommand { CommandText = "select distinct * from " + tableName };
            var adapter = new OleDbDataAdapter(_fCommand.CommandText, _fConnection);
            var aTable = new DataTable { Locale = CultureInfo.InvariantCulture };
            try
            {
                adapter.Fill(aTable);
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            aComboBox.DataSource = aTable;
            aComboBox.DisplayMember = "product_name";
            aComboBox.ValueMember = "ID";
        }

        public void  AddItemFirma(ComboBox aComboBox, int id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = new OleDbCommand
            {
                CommandText =
                    "select distinct a.* from firma a,category b where a.category_id=b.id and b.id=" +
                    Convert.ToString(id)
            };
            var adapter = new OleDbDataAdapter(_fCommand.CommandText, _fConnection);
            var aTable = new DataTable { Locale = CultureInfo.InvariantCulture };
            try
            {
                adapter.Fill(aTable);
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            aComboBox.DataSource = aTable;
            aComboBox.DisplayMember = "firm_name";
            aComboBox.ValueMember = "ID";
        }

        public void AddItemProduct(ComboBox aComboBox, int id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = new OleDbCommand
            {
                CommandText =
                    "select distinct a.* from product a,firma c where c.id=a.firma_id and c.id=" +
                    Convert.ToInt32(id)
            };

            var adapter = new OleDbDataAdapter(_fCommand.CommandText, _fConnection);
            var aTable = new DataTable { Locale = CultureInfo.InvariantCulture };
            try
            {
                adapter.Fill(aTable);
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            aComboBox.DataSource = aTable;
            aComboBox.DisplayMember = "product_name";
            aComboBox.ValueMember = "ID";
        }
        #endregion
        #region Select

        public string SelectField(string tableName, string field, string condition, string value)
        {
            string result = String.Empty;
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "select " + field + " from " + tableName + " where " + condition + "='" + value + "'";
            _fConnection.Open();
            _fReader = _fCommand.ExecuteReader();
            while (_fReader != null && _fReader.Read())
            {
                result = _fReader[field].ToString();
            }
            return result;
        }

        public string SelectField(string tableName, string field, string condition, int value)
        {
            string result = String.Empty;
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "select " + field + " from " + tableName + " where " + condition + "=" + value;
            _fConnection.Open();
            _fReader = _fCommand.ExecuteReader();
            while (_fReader != null && _fReader.Read())
            {
                result = _fReader[field].ToString();
            }
            if (result.Length > 0)
            {
                return result;
            }
            return "0";
        }

        public string SelectField(string value)
        {
            string result = String.Empty;
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "select id from firma where id=" + value + "";
            _fConnection.Open();
            _fReader = _fCommand.ExecuteReader();
            while (_fReader != null && _fReader.Read())
            {
                result = _fReader["id"].ToString();
            }
            return result;
        }

        public string SelectFieldQ(string query)
        {
            string result = String.Empty;
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = query;
            _fConnection.Open();
            _fReader = _fCommand.ExecuteReader();
            while (_fReader != null && _fReader.Read())
            {
                result = _fReader[0].ToString();
            }
            if (result == "")
            {
                return "0";
            }
            return result;
        }

        #endregion
        #region Insert

        public void InsertValueVozvrat1(string zakupka, string count1, string summa, string cdate, string date)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into vozvrat1(zak_id,count1,summa,datec1,datec) values(" + zakupka + "," + count1 + "," + summa + ",'" + cdate + "','" + date + "')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            
            _fConnection.Close();
        }

        public void InsertValueRas1(string zakupka, string ras, string ostatok, string datec)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into ras1(zak_id,ras,ostatok,datec) values(" + zakupka + "," + ras + "," + ostatok + ",'" + datec + "')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueRas(string main, string ras, string ostatok,string datec)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into ras(main_id,ras,ostatok,datec) values(" + main + "," + ras + ","+ostatok+",'" + datec + "')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueFirma(string skladid,string categoryid,string firmname)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into firma(sklad_id,category_id,firm_name) values("+skladid+","+categoryid+",'" + firmname + "')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueAngar(string description)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into ANGAR(description) values('" + description + "')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueCategory(string skladid,string description)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into category(sklad_id,description) values("+skladid+",'" + description + "')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueCustomer(string fio, string organization,string address,string telephone)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into customer(description,organization,address,telephone) values('" + fio + "','" + organization + "','"+address+"','"+telephone+"')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueProduct(string sklad, string category, string firma, string productname,string price,string count)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into product(sklad_id,category_id,firma_id,product_name,price,count1) values(" + sklad + "," + category + "," + firma + ",'" + productname + "',"+price+","+count+")";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueMain(string customer,string sklad, string category, string firma, string product, string count1,string count2,string flag,string summa,string datec )
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into main(customer_id,sklad_id,category_id,firm_id,product_id,count1,count2,flag,summa,datec,ostatok,data) values("+customer+"," + sklad + "," + category + "," + firma + "," + product + "," + count1+ ","+count2+"," + flag + ","+summa+",'"+datec+"',"+summa+",'"+DateTime.Today.ToShortDateString()+"')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueVozvrat(string main, string count1, string summa, string cdate,string date)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into vozvrat(main_id,count1,summa,datec1,datec) values(" + main + ","+count1+"," + summa + ",'" + cdate + "','"+date+"')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValueSklad(string sklad, string category,string firma,string product,string count1,string count2,string summa, string date)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into sklad(sklad_id,category_id,firma_id,product_id,count1,count2,summa,datec,ostatok) values(" + sklad + ","+category+","+firma+","+product+"," + count1 + ","+count2+"," + summa + ",'" + date + "'," + summa + ")";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValues(string main_id, double raschet, double ostatok, string date)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into ras(main_id,raschet,ostatok,data) values(" + main_id + "," + Convert.ToString(raschet) + "," + Convert.ToString(ostatok) + ",'" + date + "')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValues(int zakaz, int count, double total, double summa, string date)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into vozvrat(main_id,count1,count2,summa,cdate) values(" + Convert.ToString(zakaz) + "," + Convert.ToString(count) + "," + Convert.ToString(total) + "," + Convert.ToString(summa) + ",'" + date + "')";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void InsertValues(int category, int firma, string productName, string price, string count)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "insert into product(category_id,firma_id,product_name,price,count1) values(" + Convert.ToString(category) + "," + Convert.ToString(firma) + ",'" + productName + "'," + price + "," + count + ")";
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        #endregion
        #region Update

        public void UpdateValueAngar(string sklad,string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update angar set description='" + sklad + "' where id=" + id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void UpdateValueCategory(string sklad,string category, string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update category set description='" + category + "',sklad_id="+sklad+" where id=" + id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void UpdateValueClient(string fio, string organization,string address,string phone,string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update customer set description='" + fio + "',organization='" + organization + "',address='"+address+"',telephone='"+phone+"' where id=" + id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void UpdateValueFirma(string Firma, string sklad, string category, string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update firma set firm_name='" + Firma+ "',sklad_id=" + sklad + ",category_id=" + category + " where id=" + id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void UpdateValueProduct(string product, string sklad, string category,string firma,string price,string count, string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update product set product_name='" + product + "',sklad_id=" + sklad + ",category_id=" + category + ",firma_id="+firma+",price="+price+",count1="+count+" where id=" + id;
            _fConnection.Open();
            _fCommand.ExecuteNonQuery();
            _fConnection.Close();
        }

        public void UpdateValueMain(string customer,string sklad,string category,string firma,string product,string count1,string count2,string flag,string summa,string date,string raschet,string ostatok,string data,string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update main set customer_id="+customer+",sklad_id="+sklad+",category_id="+category+",product_id="+product+",count1="+count1+",count2="+count2+",flag="+flag+",summa="+summa+",datec='"+date+"',raschet="+raschet+",ostatok="+ostatok+",data='"+data+"' where id="+id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void UpdateValueMain(string count1, string summa,string ostatok,string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update main set count1=" + count1 + ",summa=" + summa + ",ostatok="+ostatok+" where id=" + id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void UpdateValueMain(string raschet, string ostatok, string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update main set raschet=" + raschet + ",ostatok=" + ostatok + " where id=" + id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void UpdateValueZakupka(string raschet, string ostatok, string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update sklad set raschet=" + raschet + ",ostatok=" + ostatok + " where id=" + id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }

        public void UpdateValueZakupka(string sklad, string category, string firma, string product, string count1, string count2, string summa, string raschet, string ostatok, string id)
        {
            _fConnection = new OleDbConnection(Helper.ConnectionString);
            _fCommand = _fConnection.CreateCommand();
            _fCommand.CommandText = "update sklad set sklad_id=" + sklad + ",category_id=" + category + ",product_id=" + product + ",count1=" + count1 + ",count2=" + count2 + ",summa=" + summa + ",raschet=" + raschet + ",ostatok=" + ostatok + " where id=" + id;
            try
            {
                _fConnection.Open();
                _fCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                aLogMaker.Error = ex.Message;
                aLogMaker.PrintLog();
            }
            _fConnection.Close();
        }
        #endregion

       
    }
}

